﻿Imports System.Data
Imports System.Data.Odbc

Module dataPersona
    Dim dtPersona As New DataTable
    Sub listaPersonas()

        conectar()
        dtPersona.Clear()

        cmd.Connection = con
        cmd.CommandText = "Select tPersonas.ID, tPersonas.RUT, tPersonas.Nombre, tPersonas.ApPaterno, tPersonas.ApMaterno, ttiporelacion.Nombre,tPersonas.Fono,tPersonas.Celular,tPersonas.Domicilio,tPersonas.Correo "


        cmd.CommandText += "FROM tPersonas, ttiporelacion "
        cmd.CommandText += "WHERE tPersonas.IDTipoRelacion = ttiporelacion.ID"
        cmd.CommandType = CommandType.Text
        da.SelectCommand = cmd
        da.Fill(dtPersona)

        'Çargamos el Datagridview
        With frmPersona.DGV_Personas

            .DataSource = dtPersona
            .AlternatingRowsDefaultCellStyle.BackColor = Color.AntiqueWhite
            .DefaultCellStyle.Font = New Font("Verdana", 10)
            .AutoSizeColumnsMode = System.Windows.Forms.DataGridViewAutoSizeColumnsMode.DisplayedCells
            .AutoSizeRowsMode = System.Windows.Forms.DataGridViewAutoSizeRowsMode.DisplayedCells
            .ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
            .Columns(0).HeaderText = "ID"
            .Columns(0).Visible = False
            .Columns(1).ReadOnly = True
            .Columns(1).HeaderText = "RUN"
            .Columns(2).HeaderText = "Nombre"
            .Columns(3).HeaderText = "Apellido Materno"
            .Columns(4).HeaderText = "Apellido Paterno"
            .Columns(5).HeaderText = "Tipo"
            .Columns(5).Visible = False
            .Columns(6).HeaderText = "Fono"
            .Columns(7).HeaderText = "Celular"
            .Columns(8).HeaderText = "Domicilio"
            .Columns(9).HeaderText = "Email"
            .Refresh()

        End With

        desconectar()

    End Sub


    Sub listaPersonasBus(ByVal nom As String)

        conectar()
        dtPersona.Clear()

        Cursor.Current = Cursors.WaitCursor
        cmd.Connection = con
        nom = "%" + nom + "%"
        cmd.CommandText = "SELECT ID, RUT, Nombre, ApPaterno, ApMaterno,Fono,Celular,Domicilio,Correo  FROM tpersonas WHERE  Nombre LIKE '" + nom.ToUpper + "' or ApMaterno LIKE '" + nom.ToUpper + "'or ApPaterno LIKE '" + nom.ToUpper + "'"
        cmd.CommandType = CommandType.Text
        da.SelectCommand = cmd
        da.Fill(dtPersona)
        'Çargamos el Datagridview
        With frmPersona.DGV_Personas

            .DataSource = dtPersona
            .AlternatingRowsDefaultCellStyle.BackColor = Color.AntiqueWhite
            .DefaultCellStyle.Font = New Font("Verdana", 10)
            .AutoSizeColumnsMode = System.Windows.Forms.DataGridViewAutoSizeColumnsMode.DisplayedCells
            .AutoSizeRowsMode = System.Windows.Forms.DataGridViewAutoSizeRowsMode.DisplayedCells
            .ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
            .Columns(0).HeaderText = "ID"
            .Columns(0).Visible = False
            .Columns(1).ReadOnly = True
            .Columns(1).HeaderText = "RUN"
            .Columns(2).HeaderText = "Nombre"
            .Columns(3).HeaderText = "Apellido Materno"
            .Columns(4).HeaderText = "Apellido Paterno"
            .Columns(5).HeaderText = "Fono"
            .Columns(6).HeaderText = "Celular"
            .Columns(7).HeaderText = "Domicilio"
            .Columns(8).HeaderText = "Email"
            .Refresh()
        End With
        desconectar()


    End Sub

 


    Sub datosCBTipo()

        conectar()
        Dim dt2 As New DataTable

        cmd = New OdbcCommand("Select * from ttiporelacion", con)
        dr = cmd.ExecuteReader
        'executing the command and assigning it to connection 


        dt2.Load(dr, LoadOption.OverwriteChanges)
        With frmPersona.cbTipo
            .DataSource = dt2
            .DisplayMember = dt2.Columns.Item(1).Caption
            .ValueMember = dt2.Columns.Item(0).Caption
        End With

        desconectar()


    End Sub


    Sub ingresoPersona(ByVal run As String, ByVal nombre As String, ByVal apM As String, ByVal apP As String, ByVal Tipo As Integer, ByVal fono As String, ByVal celular As String, ByVal domicilio As String, ByVal correo As String)
        conectar()
        cmd = New OdbcCommand("INSERT INTO tPersonas (RUT,Nombre,ApPaterno,ApMaterno,IDTipoRelacion,Fono,Celular,Domicilio,Correo) Values('" + run.ToUpper + "','" + nombre.ToUpper + "','" + apM.ToUpper + "','" + apP.ToUpper + "','" & Tipo & "','" + fono + "','" + celular + "','" + domicilio + "','" + correo + "')", con)

        If cmd.ExecuteNonQuery Then
            listaPersonas()
        Else
            MessageBox.Show(msjNoIngresar, msjTituloModal, MessageBoxButtons.OK, MessageBoxIcon.Error)

        End If


        desconectar()
    End Sub



    Sub eliminarPersona(ByVal id As Integer)
        conectar()

        cmd = New OdbcCommand("DELETE FROM tpersonas WHERE ID = '" & id & "'", con)

        If cmd.ExecuteNonQuery Then
            listaPersonas()
        Else
            MessageBox.Show(msjNoEliminar, msjTituloModal, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If
        desconectar()


    End Sub

    Sub actualizarPersona(ByVal id As Integer, ByVal run As String, ByVal nom As String, ByVal app As String, ByVal apm As String, ByVal fono As String, ByVal celular As String, ByVal domicilio As String, ByVal correo As String)

        conectar()

        cmd = New OdbcCommand("UPDATE tpersonas set  Nombre='" + nom.ToUpper + "',ApPaterno='" + app.ToUpper + "',ApMaterno='" + apm.ToUpper + "',Fono='" + fono + "',Celular ='" + celular + "', Domicilio = '" + domicilio + "',Correo ='" + correo + "' WHERE  ID ='" & id & "'", con)

        If cmd.ExecuteNonQuery Then
            listaPersonas()
        Else
            MessageBox.Show(msjNoActualizar, msjTituloModal, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If
        desconectar()


    End Sub
End Module
